package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import com.bo.User;
import com.dao.UserDao;
import com.db.util.DBUtil;
import com.web.util.MD5;
import com.web.util.MD5Util;

public class UserDaoImpl implements UserDao{

	@Override
	public User insert(User user) throws Exception {
		String insertInto = "insert into t_user(user_name, real_name, pwd, register_date, login_date, email, phone_num, address, flag) values(?,?,?,?,?,?,?,?,?)";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			int i=0;
			ps = conn.prepareStatement(insertInto);
			ps.setString(++i, user.getUserName());
			ps.setString(++i, user.getRealName());
			ps.setString(++i, MD5Util.getMD5Code(user.getPwd()));
			ps.setTimestamp(++i, new Timestamp(new Date().getTime()));
			ps.setTimestamp(++i, new Timestamp(new Date().getTime()));
			ps.setString(++i, user.getEmail());
			ps.setString(++i, user.getPhoneNum());
			ps.setString(++i, user.getAddress());
			ps.setInt(++i, user.getFlag());
			ps.execute();
			user.setUserId(new Long(DBUtil.getLastInsertId(conn)));
			
		} catch (Exception e) {
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}
		System.out.println("product save successfully");
		return user;
		
	}

	@Override
	public User update(User user) throws Exception {
		String updateStr = "update t_user set user_name=?,real_name=?,pwd=?,email=?,phone_num=?,login_date=?, address=? where user_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			int i = 0;
			ps = conn.prepareStatement(updateStr);
			ps.setString(++i, user.getUserName());
			ps.setString(++i, user.getRealName());
			ps.setString(++i, MD5Util.getMD5Code(user.getPwd()));
			ps.setString(++i,user.getEmail());
			ps.setString(++i, user.getPhoneNum());
			ps.setTimestamp(++i, new Timestamp(user.getLoginDate().getTime()));
			ps.setString(++i, user.getAddress());
			ps.setLong(++i, user.getUserId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}

		return user;
		
	}

	@Override
	public void delete(User user) throws Exception {
		String updateStr = "delete from t_user where user_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setLong(1, user.getUserId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}
		
	}

	@Override
	public User getUserByEmail(String email,String pwd) throws Exception {
		String updateStr = "select * from t_user where email=? and pwd=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setString(1, email);
			ps.setString(2, MD5Util.getMD5Code(pwd));
			rs = ps.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setUserId(rs.getLong("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setRealName(rs.getString("real_name"));
				user.setEmail(rs.getString("email"));
				user.setPhoneNum(rs.getString("phone_num"));
				user.setFlag(rs.getInt("flag"));
				user.setLoginDate(rs.getTimestamp("login_date"));
				user.setAddress(rs.getString("address"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return null;
	}
	
	/**
	 * 	only used for auto login
	 */
	@Override
	public User getUserByEmail(String email) throws Exception {
		String updateStr = "select * from t_user where email=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setUserId(rs.getLong("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setRealName(rs.getString("real_name"));
				user.setEmail(rs.getString("email"));
				user.setPhoneNum(rs.getString("phone_num"));
				user.setFlag(rs.getInt("flag"));
				user.setLoginDate(rs.getTimestamp("login_date"));
				user.setAddress(rs.getString("address"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return null;
	}
	/**
	 * check user whether exist by email for register
	 */
	@Override
	public boolean userExistByEmail(String email) throws Exception {
		String sqlEmail = "select 1 from t_user where email=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sqlEmail);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return false;
	}
	
	/**
	 * check user whether exist by userName for register
	 */
	@Override
	public boolean userExistByName(String userName) throws Exception {
		String sqlName = "select 1 from t_user where user_name=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sqlName);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return false;
	}
}
